package com.salesmanager.core.utils;

import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Liutong on 2016/5/9.
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    //通过对单元格遍历的形式来获取信息
    public static List<Map<String,Object>> read(String path) {
        File myFile=new File(path);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        List<Map<String,Object>> list=new ArrayList<Map<String, Object>>();
        try {
//            InputStream ips = new FileInputStream(path);
            URL url = new URL(path);
            URLConnection connection = url.openConnection();
            InputStream ips = connection.getInputStream();
            HSSFWorkbook wb = new HSSFWorkbook(ips);
            HSSFSheet sheet = wb.getSheetAt(0);
            int i = 0;
            List<String> head = new ArrayList<String>();
            for (Iterator ite = sheet.rowIterator(); ite.hasNext(); ) {
                Map<String, Object> mapRow = new HashMap<String, Object>();
                HSSFRow row = (HSSFRow) ite.next();
                int j = 0;
                for (Iterator itet = row.cellIterator(); itet.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) itet.next();
                    Object object = new Object();
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            //得到Boolean对象的方法
                            object = cell.getBooleanCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            //先看是否是日期格式
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                //读取日期格式
                                object = cell.getDateCellValue();
                                object=format.format(object);
                            } else {
                                //读取数字
                                object = cell.getNumericCellValue();
                            }
                            break;
//                        case HSSFCell.CELL_TYPE_FORMULA:
//                            //读取公式
//                            object = cell.getCellFormula().get;
//                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            //读取String
                            object = cell.getRichStringCellValue().getString();
                            break;
                        default:
                            break;
                    }
                    //列头
                    if (i < 1) {
                        head.add((String) object);
                    } else {
                        if(head.size()>j) {
                            mapRow.put(head.get(j), object + "");
                        }
                    }
                    j++;
                }
                if(i>0){
                    if(mapRow.size()==head.size())
                        list.add(mapRow);
                }
                i++;

            }
        }catch (Exception e){
            logger.error("POI error",e);
        }
        if(myFile.exists())
            myFile.delete();
        return list;
    }
}
